using ABA;
using NUnit.Framework;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;

namespace FW
{
    public class DecodeExcel
    {
        public List<ExcelTableData> Decode(string filePath)

        {
            if(File.Exists(filePath))
            {
                FileInfo fileInfo = new FileInfo(filePath);
                using (var package = new ExcelPackage(fileInfo))
                {
                    var ret = new List<ExcelTableData>();
                    for(int i = 0; i <= package.Workbook.Worksheets.Count;i++)
                    {
                        ExcelWorksheet sheet = package.Workbook.Worksheets[i];
                        if (sheet.Name.StartsWith("Sheet"))
                            break;
                        var data = GetSheetExcelData(sheet);
                        ret.Add(data);
                    }

                    return ret;
                }

                
            }
            return null;

        }

        //解析Excel
        private ExcelTableData GetSheetExcelData(ExcelWorksheet sheet)
        {
            List<string>commentList = new List<string>();
            for (int i = 1; i < sheet.Dimension.End.Column; i++)
            {
                ExcelRange item = sheet.Cells[1, i];
                string value = (item.Value??"").ToString().Trim();
                if (string.IsNullOrEmpty(value))
                {
                    ExcelComment comment = item.Comment;
                    string str_comment = "";
                    if (comment != null)
                        str_comment = comment.Text.Replace("\n", " ").Replace("\r", " ");
                    str_comment = value + str_comment;
                    commentList.Add(str_comment);
                }
                else
                    break;
            }

            int columCount = commentList.Count;
            List<string>fieldNames = new List<string>();  //字段名
            List<string>typeNames = new List<string>();   //字段类型
            for(int i = 1;i< columCount; i++)
            {
                fieldNames.Add((sheet.Cells[2,i].Value??" ").ToString().Trim());
                typeNames.Add((sheet.Cells[3, i].Value ?? " ").ToString().Trim());
            }

            //以下就是每条记录了
            List <string[]>valueList = new List<string[]>();
            int maxRow = sheet.Dimension.End.Row;

            for(int i = 4; i < maxRow; i++)
            {
                var obj = sheet.Cells[i, 1].Value;
                if (obj != null)
                {
                    string[] arrayItem = new string[columCount];
                    arrayItem[0] = obj.ToString();
                    for (int col = 2; col <= columCount; col++)
                    {
                        arrayItem[col - 1] = sheet.Cells[i, col].Text ==
                            null ? "" : sheet.Cells[i, col].Text;

                        valueList.Add(arrayItem);
                    }

                }
                else
                    break;
            }

            ExcelTableData data = new ExcelTableData(sheet.Name,commentList,typeNames,valueList,fieldNames);
            return data;
        }
    }

    public class ExcelTableData
    {
        public string _tableName;
        public List<string> _commentList;
        public List<string> _typeNameList;
        public List<string[]> _valueList;
        public List<string> _fieldNameList;

        public ExcelTableData(string tableName, List<string> _commentList, List<string> _typeNameList, List<string[]>_valueList,List<string> _fieldNameList)
        {
            _tableName = tableName;
            this._commentList = _commentList;  
            this._typeNameList = _typeNameList;
            this._valueList = _valueList;
            this._fieldNameList = _fieldNameList;
        }

    }
}